package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;

public class student {

    private String Saccount;

    private int Sid;

    private int Cno;

    private int Cscore;

    private HashMap<String,String> nature;

    public HashMap<String,String> getNature() {

        nature=resources.findResourceByCate("Student");

        return nature;
    }

    public void setNature(HashMap<String,String> nature) {
        this.nature = nature;
    }

    public student(String saccount){
        Saccount = saccount;
    }

    public String getSaccount() {
        return Saccount;
    }

    public void setSaccount(String saccount) {
        Saccount = saccount;
    }

    public int getCno() {
        return Cno;
    }

    public void setCno(int cno) {
        Cno = cno;
    }

    public int getCscore() {
        return Cscore;
    }

    public void setCscore(int cscore) {
        Cscore = cscore;
    }

    public static int addClassByAccount(String account,int cno){

        Connection con=jdbcUtil.jdbcUtil.getConnection();

        PreparedStatement pstm=null;

        ResultSet rs=null;

        int num=0;

        try {
            pstm=con.prepareStatement("INSERT INTO student (Saccount,Cno) VALUES (?,?)");

            pstm.setString(1,account);
            pstm.setInt(2,cno);

            num=pstm.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("失败--->student.java-->addClassByAccount()");
        }finally {
            jdbcUtil.jdbcUtil.close(null,pstm,rs);
        }

        return num;
    }

    public static HashMap<classes,Integer> seeScore(String account){
        Connection con=jdbcUtil.jdbcUtil.getConnection();

        PreparedStatement pstm=null;

        ResultSet rs=null;

        HashMap<classes,Integer> hashMap=new HashMap<>();

        try {
            pstm=con.prepareStatement("SELECT s.Cscore,c.`Cno`,c.`Cname` FROM student s JOIN classes c ON s.Saccount=? AND c.`Cno`=s.`Cno`");

            pstm.setString(1,account);

            rs=pstm.executeQuery();

            while(rs.next()){

                int Cscore=rs.getInt("Cscore");

                int Cno=rs.getInt("Cno");

                String Cname=rs.getString("Cname");

/*                if(Cname==null){
                    Cname="暂无数据";
                }*/

                classes cl=new classes(Cno,Cname);

                hashMap.put(cl,Cscore);

            }
        } catch (SQLException e) {
            e.printStackTrace();
            e.printStackTrace();
            System.out.println("失败-->student.java-->seeScore()");
        }finally {
            jdbcUtil.jdbcUtil.close(null,pstm,rs);
        }

        return hashMap;
    }

    public static HashMap<users,classes> allStudentInfo(){
        Connection con=jdbcUtil.jdbcUtil.getConnection();

        PreparedStatement pstm=null;

        ResultSet rs=null;

        HashMap<users,classes> hashMap=new HashMap<>();

        try {
            pstm=con.prepareStatement("SELECT u.*,c.Cname,c.`Cno` FROM users u  JOIN (SELECT c.*,s.`Saccount` FROM student s JOIN classes c ON s.`Cno`=c.`Cno`) c ON u.`Ucate`='Student' AND u.`Uaccount`=c.Saccount");

            rs=pstm.executeQuery();


            while(rs.next()){
                String uaccount=rs.getString("Uaccount");
                String uname=rs.getString("Uname");
                String upassword=rs.getString("Upassword");
                int uage=rs.getInt("Uage");
                String usex=rs.getString("Usex");
                String ucate=rs.getString("Ucate");
                String cname=rs.getString("cname");
                int cno=rs.getInt("Cno");

                if(uaccount==null){
                    uaccount="暂无数据";
                }
                if(uname==null){
                    uname="暂无数据";
                }
                if(upassword==null){
                    upassword="暂无数据";
                }
                if(usex==null){
                    usex="暂无数据";
                }
                if(ucate==null){
                    ucate="暂无数据";
                }
                if(cname==null){
                    cname="暂无数据";
                }


                users us=new users(uaccount,uname,upassword,uage,usex,ucate);
                classes cl=new classes(cno,cname);

                hashMap.put(us,cl);

            }
        } catch (SQLException e) {
            e.printStackTrace();
            e.printStackTrace();
            System.out.println("失败-->student.java-->allStudentInfo()");
        }finally {
            jdbcUtil.jdbcUtil.close(null,pstm,rs);
        }
        return hashMap;
    }

}
